﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data;

namespace DAO
{
    public class TaiKhoanDAO
    {
        DataProvider provider;

        public TaiKhoanDAO()
        {
            provider = new DataProvider();
        }

        public TaiKhoan KiemTraDangNhap(TaiKhoan taiKhoan)
        {
            string sqlStatement = "SELECT * FROM TaiKhoan WHERE TenTaiKhoan='" + taiKhoan.TenTaiKhoan
                + "' AND MatKhau='" + taiKhoan.MatKhau + "' AND DaXoa=0";

            DataTable dt = provider.ExecuteQuery(sqlStatement);
            if (dt != null)
            {
                if (dt.Rows.Count == 1)
                {
                    TaiKhoan tk = new TaiKhoan();
                    tk.MaTaiKhoan = dt.Rows[0]["MaTaiKhoan"].ToString();
                    return LayTaiKhoanTheoMaTaiKhoan(tk);
                }
                return null;
            }
            return null;
        }
                
        public TaiKhoan LayTaiKhoanTheoMaTaiKhoan(TaiKhoan taiKhoan)
        {
            TaiKhoan tk = null;

            string sqlStatement = "SELECT * FROM TaiKhoan WHERE MaTaiKhoan=" + taiKhoan.MaTaiKhoan;
            DataTable dt = provider.ExecuteQuery(sqlStatement);
            if (dt != null)
            {
                tk = new TaiKhoan();
                tk.MaTaiKhoan = dt.Rows[0]["MaTaiKhoan"].ToString();
                tk.TenTaiKhoan = dt.Rows[0]["TenTaiKhoan"].ToString();
                tk.MatKhau = dt.Rows[0]["MatKhau"].ToString();
                tk.HoTen = dt.Rows[0]["HoTen"].ToString();
                tk.CMND = dt.Rows[0]["CMND"].ToString();
                tk.GioiTinh = dt.Rows[0]["GioiTinh"].ToString();
                tk.DienThoai = dt.Rows[0]["DienThoai"].ToString();
                tk.Email = dt.Rows[0]["Email"].ToString();
                tk.SoNha = dt.Rows[0]["SoNha"].ToString();
                tk.TenDuong = dt.Rows[0]["TenDuong"].ToString();
                tk.PhuongXa = dt.Rows[0]["PhuongXa"].ToString();
                tk.QuanHuyen = dt.Rows[0]["QuanHuyen"].ToString();
                tk.ThanhPho = dt.Rows[0]["ThanhPho"].ToString();
                tk.SoLanThamGiaDauGia = Int32.Parse(dt.Rows[0]["SoLanThamGiaDauGia"].ToString());
                tk.DuocQuyenThamGiaDauGia = bool.Parse(dt.Rows[0]["DuocQuyenThamGiaDauGia"].ToString());
                tk.MaKichHoatTaiKhoan = dt.Rows[0]["MaKichHoatTaiKhoan"].ToString();
                tk.DaKichHoat = bool.Parse(dt.Rows[0]["DaKichHoat"].ToString());
                tk.PhanQuyen = Int32.Parse(dt.Rows[0]["PhanQuyen"].ToString());
                tk.DaXoa = bool.Parse(dt.Rows[0]["DaXoa"].ToString());
                return tk;
            }
            return tk;
        }

        public bool KiemTraTenTaiKhoanTrung(TaiKhoan taiKhoan)
        {
            string sqlStatement = "SELECT * FROM TaiKhoan WHERE TenTaiKhoan='" + taiKhoan.TenTaiKhoan + "'";
            DataTable dt = provider.ExecuteQuery(sqlStatement);
            if (dt.Rows.Count == 0)
                return true;
            return false;
        }

        public bool KiemTraEmailTrung(TaiKhoan taiKhoan)
        {
            string sqlStatement = "SELECT * FROM TaiKhoan WHERE Email=N'" + taiKhoan.Email + "' AND DaXoa=0";
            DataTable dt = provider.ExecuteQuery(sqlStatement);
            if (dt.Rows.Count == 0)
                return true;
            return false;
        }

        private string LayMaTaiKhoanMoi()
        {
            string maTK = "TK";

            string sqlStatement = "SELECT COUNT(*) FROM TaiKhoan";
            DataTable dt = provider.ExecuteQuery(sqlStatement);
            if (dt != null)
            {
                int noTK = Int32.Parse(dt.Rows[0][0].ToString()) + 1;
                int temp = noTK;
                int n = 0;
                while (temp > 0)
                {
                    temp = temp / 10;
                    n++;
                }
                while (n <= 11)
                {
                    maTK += "0";
                    n++;
                }
                maTK += noTK;
            }

            return maTK;
        }

        public int ThemTaiKhoan(TaiKhoan taiKhoan)
        {
            if (!KiemTraTenTaiKhoanTrung(taiKhoan) || !KiemTraEmailTrung(taiKhoan))
                return 2;

            string sqlStatement = "INSERT INTO TaiKhoan VALUES "
                + "('" + LayMaTaiKhoanMoi() + "', '" + taiKhoan.TenTaiKhoan + "', N'" + taiKhoan.MatKhau
                + "', N'" + taiKhoan.HoTen + "', N'" + taiKhoan.CMND + "', N'" + taiKhoan.GioiTinh 
                + "', N'" + taiKhoan.DienThoai + "', N'" + taiKhoan.Email + "', N'" + taiKhoan.SoNha 
                + "', N'" + taiKhoan.TenDuong + "', N'" + taiKhoan.PhuongXa + "', N'" + taiKhoan.QuanHuyen 
                + "', N'" + taiKhoan.ThanhPho + "', " + taiKhoan.SoLanThamGiaDauGia + ", " + taiKhoan.DuocQuyenThamGiaDauGia
                + ", N'" + taiKhoan.MaKichHoatTaiKhoan + "', " + taiKhoan.DaKichHoat 
                + ", " + taiKhoan.PhanQuyen + ", 0)";

            return provider.ExecuteNonQuery(sqlStatement);
        }

        public int CapNhatTaiKhoan(TaiKhoan taiKhoan)
        {
            string sqlStatement = "UPDATE TaiKhoan SET MatKhau=N'" + taiKhoan.MatKhau
                + "', HoTen=N'" + taiKhoan.HoTen + "', CMND=N'" + taiKhoan.CMND + "', GioiTinh=N'" + taiKhoan.GioiTinh
                + "', DienThoai=N'" + taiKhoan.DienThoai + "', Email=N'" + taiKhoan.Email 
                + "', SoNha=N'" + taiKhoan.SoNha + "', TenDuong=N'" + taiKhoan.TenDuong
                + "', PhuongXa=N'" + taiKhoan.PhuongXa + "', QuanHuyen=N'" + taiKhoan.QuanHuyen
                + "', ThanhPho=N'" + taiKhoan.ThanhPho + "', SoLanThamGiaDauGia=" + taiKhoan.SoLanThamGiaDauGia
                + ", DuocQuyenThamGiaDauGia=" + taiKhoan.DuocQuyenThamGiaDauGia + ", MaKichHoatTaiKhoan=N'" + taiKhoan.MaKichHoatTaiKhoan
                + "', DaKichHoat=" + taiKhoan.DaKichHoat + ", PhanQuyen=" + taiKhoan.PhanQuyen 
                + " WHERE MaTaiKhoan=" + taiKhoan.MaTaiKhoan;

            return provider.ExecuteNonQuery(sqlStatement);
        }

        public int XoaTaiKhoan(TaiKhoan taiKhoan)
        {
            string sqlStatement = "UPDATE TaiKhoan SET DaXoa=1 WHERE MaTaiKhoan=" + taiKhoan.MaTaiKhoan;

            return provider.ExecuteNonQuery(sqlStatement);
        }
    }
}
